﻿#include "sqlhelper.h"
#include "global.h"
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlRecord>


SqlHelper::SqlHelper()
{
}

SqlHelper* SqlHelper::instance()
{
    return sqlInstance();  // 返回线程安全的静态对象
}

bool SqlHelper::initDB(QString dbName)
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(dbName);
    return db.open();
}

bool SqlHelper::selectUserByName(SysUser& user, QString name)
{
    QString fields = user.getFieldList().join(", ");
    QString sql = QString("select %1 from %2 where 1 = 1 and user_name like '%3' order by id asc limit 1")
            .arg(fields).arg(user.getTableName()).arg(name);

    QSqlQuery query;
    query.prepare(sql);
    query.exec();
    QSqlRecord record = query.record();
    if (query.next())
    {
        QMap<QString, QVariant> dataMap;

        for(int i = 0; i < record.count(); i++)
        {
            QString field = record.fieldName(i);
            QVariant data = query.value(i);
            dataMap.insert(field, data);
        }
        user.setData(dataMap);
        return true;
    };

    return false;
}

//更新用户
bool SqlHelper::updateEntity(TableEntity* pEntity)
{
    if(pEntity == NULL)
        return false;
    QString sql = pEntity->getUpdateString();
    QSqlQuery query;
    query.prepare(sql);
    return query.exec();
}

bool SqlHelper::selectEntityById(TableEntity* pEntity)
{
    if(pEntity == NULL)
        return false;

    QString fields = pEntity->getFieldList().join(", ");
    QString sql = QString("select %1 from %2 where id = %3 order by id asc limit 1")
            .arg(fields).arg(pEntity->getTableName()).arg(pEntity->getId());

    QSqlQuery query;
    query.prepare(sql);
    query.exec();
    QSqlRecord record = query.record();
    if (query.next())
    {
        QMap<QString, QVariant> dataMap;

        for(int i = 0; i < record.count(); i++)
        {
            QString field = record.fieldName(i);
            QVariant data = query.value(i);
            dataMap.insert(field, data);
        }
        pEntity->setData(dataMap);
        return true;
    };
    return false;
}

//插入用户
bool SqlHelper::insertEntity(TableEntity* pEntity)
{
    if(pEntity == NULL)
        return false;
    QString sql = pEntity->getInsertString();
    QSqlQuery query;
    query.prepare(sql);
    if(query.exec())
    {
        pEntity->setId(query.lastInsertId().toInt());
        return true;
    }
    return false;
}

//删除用户
bool SqlHelper::deleteEntity(TableEntity* pEntity)
{
    if(pEntity == NULL)
        return false;
    QString sql = QString("delete from %1 where 1 = 1 and id = %2")
            .arg(pEntity->getTableName()).arg(pEntity->getId());
    QSqlQuery query;
    query.prepare(sql);
    return query.exec();
}


void SqlHelper::getList(QList<QMap<QString, QVariant >>& result,
                        QString tableName,
                        QString fields,
                        QStringList condiList,
                        QString orderField,
                        bool isAsc)
{
    QString whereSql = QString(" 1 = 1 ");

    foreach (QString condi, condiList)
    {
       whereSql += QString(" and %1 ").arg(condi) ;
    }

    QString orderSql = "";
    if(orderField != nullptr && !orderField.isEmpty())
        orderSql = QString(" order by %1 %2" ).arg(orderField).arg(isAsc ? "asc" : "desc");

    QString sql = QString("select %1 from %2 where %3 " ).arg(fields).arg(tableName).arg(whereSql)
            + orderSql;

    QSqlQuery query;
    query.prepare(sql);
    query.exec();
    QSqlRecord record = query.record();
    while (query.next()) {
        QMap<QString, QVariant> dataMap;

        for(int i = 0; i < record.count(); i++)
        {
            QString field = record.fieldName(i);
            QVariant data = query.value(i);
            dataMap.insert(field, data);
        }
        result.append(dataMap);
    };

}


void SqlHelper::getList(QList<QMap<QString, QVariant >>& result,   //查询结果
                        int& count,                                //总数量
                        QString tableName,                         //查询表名
                        QString fields,                            //查询字段
                        QStringList condiList,                     //查询条件
                        int page,                                  //查询页码
                        int limit,                                 //每页数量
                        QString orderField,                        //排序字段
                        bool isAsc)                                //排序方式
{
    QString whereSql = QString(" 1 = 1 ");

    foreach (QString condi, condiList)
    {
       whereSql += QString(" and %1 ").arg(condi) ;
    }

    QString sql = QString("select count(*) from %1 where %2 " ).arg(tableName).arg(whereSql);
    QSqlQuery query;
    query.prepare(sql);
    if(!query.exec())
    {
        result.clear();
        count = 0;
        return;
    }
    if(query.next())
    {
        count = query.value(0).toInt();
    }

    QString orderSql = "";
    if(orderField != nullptr && !orderField.isEmpty())
        orderSql = QString(" order by %1 %2" ).arg(orderField).arg(isAsc ? "asc" : "desc");

    sql = QString("select %1 from %2 where %3 %4 limit %5 offset %6" )
            .arg(fields).arg(tableName).arg(whereSql).arg(orderSql).arg(limit).arg(page-1);

    query.prepare(sql);
    query.exec();
    QSqlRecord record = query.record();
    while (query.next()) {
        QMap<QString, QVariant> dataMap;

        for(int i = 0; i < record.count(); i++)
        {
            QString field = record.fieldName(i);
            QVariant data = query.value(i);
            dataMap.insert(field, data);
        }
        result.append(dataMap);
    };
}

void SqlHelper::getUserList(QList<SysUser>& list, QStringList condiList, QString orderField, bool isAsc)
{
    list.clear();
    SysUser dao;
    QString fields = dao.getFieldList().join(", ");
    QList<QMap<QString, QVariant>> result;

    getList(result, dao.getTableName(), fields, condiList, orderField, isAsc);

    foreach (auto valueMap, result) {
        SysUser entity;
        entity.setData(valueMap);
        list.append(entity);
    }
}

void SqlHelper::getUserList(QList<SysUser>& list, int& count,
                            int page, int limit, QStringList condiList, QString orderField, bool isAsc)
{
    list.clear();
    SysUser dao;
    QString fields = dao.getFieldList().join(", ");
    QList<QMap<QString, QVariant>> result;

    QString order = GlobalFunc::getFieldName(orderField);
    getList(result, count, dao.getTableName(), fields, condiList, page, limit, order, isAsc);

    foreach (auto valueMap, result) {
        SysUser entity;
        entity.setData(valueMap);
        list.append(entity);
    }
}

void SqlHelper::getDeptList(QList<SysDept>& list, QStringList condiList, QString orderField, bool isAsc)
{
    list.clear();
    SysDept dao;
    QString fields = dao.getFieldList().join(", ");
    QList<QMap<QString, QVariant>> result;

    getList(result, dao.getTableName(), fields, condiList, orderField, isAsc);

    foreach (auto valueMap, result) {
        SysDept entity;
        entity.setData(valueMap);
        list.append(entity);
    }
}

void SqlHelper::getDictList(QList<SysDictionary>& list, QStringList condiList, QString orderField, bool isAsc)
{
    list.clear();
    SysDictionary dao;
    QString fields = dao.getFieldList().join(", ");
    QList<QMap<QString, QVariant>> result;

    getList(result, dao.getTableName(), fields, condiList, orderField, isAsc);

    foreach (auto valueMap, result) {
        SysDictionary entity;
        entity.setData(valueMap);
        list.append(entity);
    }
}

void SqlHelper::getDictValueList(QList<SysDicValue>& list, QStringList condiList, QString orderField, bool isAsc)
{
    list.clear();
    SysDicValue dao;
    QString fields = dao.getFieldList().join(", ");
    QList<QMap<QString, QVariant>> result;

    getList(result, dao.getTableName(), fields, condiList, orderField, isAsc);

    foreach (auto valueMap, result) {
        SysDicValue entity;
        entity.setData(valueMap);
        list.append(entity);
    }
}

//获取菜单列表
void SqlHelper::getMenuList(QList<SysMenu>& list, QStringList condiList, QString orderField, bool isAsc)
{
    list.clear();
    SysMenu dao;
    QString fields = dao.getFieldList().join(", ");
    QList<QMap<QString, QVariant>> result;

    getList(result, dao.getTableName(), fields, condiList, orderField, isAsc);

    foreach (auto valueMap, result) {
        SysMenu entity;
        entity.setData(valueMap);
        list.append(entity);
    }
}

//获取角色列表
void SqlHelper::getRoleList(QList<SysRole>& list, QStringList condiList, QString orderField, bool isAsc)
{
    list.clear();
    SysRole dao;
    QString fields = dao.getFieldList().join(", ");
    QList<QMap<QString, QVariant>> result;

    getList(result, dao.getTableName(), fields, condiList, orderField, isAsc);

    foreach (auto valueMap, result) {
        SysRole entity;
        entity.setData(valueMap);
        list.append(entity);
    }
}

//获取角色菜单关联列表
void SqlHelper::getRoleMenuList(QList<SysRoleMenu>& list, QStringList condiList, QString orderField, bool isAsc)
{
    list.clear();
    SysRoleMenu dao;
    QString fields = dao.getFieldList().join(", ");
    QList<QMap<QString, QVariant>> result;

    getList(result, dao.getTableName(), fields, condiList, orderField, isAsc);

    foreach (auto valueMap, result) {
        SysRoleMenu entity;
        entity.setData(valueMap);
        list.append(entity);
    }
}

//根据角色获取权限菜单
void SqlHelper::getMenuByRoleId(QList<SysMenu>& menuList, int roleId)
{
    //select * from sys_menu where id in (select menu_id from sys_role_menu srm where srm.role_id = "1")
    SysMenu dao;
    SysRoleMenu daoRm;
    QString fields = dao.getFieldList().join(", ");

    QString sql = QString("select %1 from %2 where id in (select menu_id from %3 srm where srm.role_id = %4")
            .arg(fields)
            .arg(dao.getTableName())
            .arg(daoRm.getTableName())
            .arg(roleId);

    QSqlQuery query;
    query.prepare(sql);
    query.exec();
    QSqlRecord record = query.record();
    while (query.next()) {
        QMap<QString, QVariant> dataMap;
        for(int i = 0; i < record.count(); i++)
        {
            QString field = record.fieldName(i);
            QVariant data = query.value(i);
            dataMap.insert(field, data);
        }
        SysMenu entity;
        entity.setData(dataMap);
        menuList.append(entity);
    };
}

//根据条件删除
bool SqlHelper::deleteTable(QString tableName, QStringList condiList)
{

    QString whereSql = QString(" 1 = 1 ");

    foreach (QString condi, condiList)
    {
       whereSql += QString(" and %1 ").arg(condi) ;
    }

    QString sql = QString("delete from %1 where %2").arg(tableName).arg(whereSql);

    QSqlQuery query;
    query.prepare(sql);
    return query.exec();
}


//获取用户角色关联列表
void SqlHelper::getUserRoleList(QList<SysUserRole>& list, QStringList condiList, QString orderField, bool isAsc)
{
    list.clear();
    SysUserRole dao;
    QString fields = dao.getFieldList().join(", ");
    QList<QMap<QString, QVariant>> result;

    getList(result, dao.getTableName(), fields, condiList, orderField, isAsc);

    foreach (auto valueMap, result) {
        SysUserRole entity;
        entity.setData(valueMap);
        list.append(entity);
    }
}


//获取日志分页表
void SqlHelper::getLogList(QList<SysLog>& list, int& count,
                            int page, int limit, QStringList condiList, QString orderField, bool isAsc)
{
    list.clear();
    SysLog dao;
    QString fields = dao.getFieldList().join(", ");
    QList<QMap<QString, QVariant>> result;

    QString order = GlobalFunc::getFieldName(orderField);
    getList(result, count, dao.getTableName(), fields, condiList, page, limit, order, isAsc);

    foreach (auto valueMap, result) {
        SysLog entity;
        entity.setData(valueMap);
        list.append(entity);
    }
}


//获取用户消息列表
void SqlHelper::getSysUserMessageList(QList<SysUserMessage>& list, QStringList condiList, QString orderField, bool isAsc)
{
    list.clear();
    SysUserMessage dao;
    QString fields = dao.getFieldList().join(", ");
    QList<QMap<QString, QVariant>> result;

    getList(result, dao.getTableName(), fields, condiList, orderField, isAsc);

    foreach (auto valueMap, result) {
        SysUserMessage entity;
        entity.setData(valueMap);
        list.append(entity);
    }
}

//获取用户消息列表分页
void SqlHelper::getSysUserMessageList(QList<SysUserMessage>& list, int& count, int page, int limit, QStringList condiList, QString orderField, bool isAsc)
{
    list.clear();
    SysUserMessage dao;
    QString fields = dao.getFieldList().join(", ");
    QList<QMap<QString, QVariant>> result;

    QString order = GlobalFunc::getFieldName(orderField);
    getList(result, count, dao.getTableName(), fields, condiList, page, limit, order, isAsc);

    foreach (auto valueMap, result) {
        SysUserMessage entity;
        entity.setData(valueMap);
        list.append(entity);
    }
}

//获取系统消息列表
void SqlHelper::getSysMessageList(QList<SysMessage>& list, QStringList condiList, QString orderField, bool isAsc)
{
    list.clear();
    SysMessage dao;
    QString fields = dao.getFieldList().join(", ");
    QList<QMap<QString, QVariant>> result;

    getList(result, dao.getTableName(), fields, condiList, orderField, isAsc);

    foreach (auto valueMap, result) {
        SysMessage entity;
        entity.setData(valueMap);
        list.append(entity);
    }
}
//获取系统消息列表分页
void SqlHelper::getSysMessageList(QList<SysMessage>& list, int& count, int page, int limit, QStringList condiList, QString orderField, bool isAsc)
{
    list.clear();
    SysMessage dao;
    QString fields = dao.getFieldList().join(", ");
    QList<QMap<QString, QVariant>> result;

    QString order = GlobalFunc::getFieldName(orderField);
    getList(result, count, dao.getTableName(), fields, condiList, page, limit, order, isAsc);

    foreach (auto valueMap, result) {
        SysMessage entity;
        entity.setData(valueMap);
        list.append(entity);
    }
}

